﻿USE [OddsRecorder]
GO
/****** Object:  UserDefinedFunction [dbo].[Fun_OddsIssueItemoOddsValueByKeyWord]    Script Date: 11/21/2011 08:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[Fun_OddsIssueItemoOddsValueByKeyWord] 
(@keyWord VARCHAR(50)
)
RETURNS TABLE 
AS
RETURN 
(
		SELECT i.IssueRealID, it.IndexInIssue, m.MatchTypeName, ht.TeamName AS HomeTeam, 
				vt.TeamName AS VisitingTeam, v.IndexInIssueItem, c.CompanyName, v.OddsWin, 
				v.OddsDraw, v.OddsLose, it.MatchResult, v.OddsDatetime, i.IssueID, 
				it.IssueItemID, v.OddsValueID, c.CompanyID
		FROM OddsValue AS v 
			LEFT OUTER JOIN OddsIssueItem AS it ON v.IssueItemID = it.IssueItemID 
			LEFT OUTER JOIN OddsIssue AS i ON it.IssueID = i.IssueID 
			LEFT OUTER JOIN OddsCompany AS c ON v.CompanyID = c.CompanyID 
			LEFT OUTER JOIN OddsMatchType AS m ON it.MatchTypeID = m.MatchTypeID 
			LEFT OUTER JOIN OddsTeam AS ht ON it.HomeTeamID = ht.TeamID 
			LEFT OUTER JOIN OddsTeam AS vt ON it.VisitingTeamID = vt.TeamID
		WHERE CHARINDEX(m.MatchTypeName, @keyWord)>0 AND OddsWin > 0 
			AND OddsDraw > 0
			AND OddsLose > 0
)
